
#Load Data
data<-data_student_raw 
data$n_hs_town_group<-data$n_hs_town
data$n_hs_town_group[data$n_hs_town>=4 & data$n_hs_town<=15]<-"4-15"
data$n_hs_town_group[data$n_hs_town>15]<-"16+"
data$n_hs_town_group<-with(data, reorder(n_hs_town_group, n_hs_town))

#
pacman::p_load(openxlsx)
#Load pop data
setwd(wd_data_final)
pop<-read.xlsx("Population.xlsx")
siruta<-read.xlsx("SIRUTA2-3.xlsx") %>% filter(MED==3) %>% select(SIRUTA,SIRSUP) 

###
data<-data %>% 
  base::merge(siruta,by.x=c("Cod_SIRUTA_hs_bac"),by.y=c("SIRUTA"),all.x=T) %>%
  mutate(SIRSUP=ifelse(is.na(SIRSUP),Cod_SIRUTA_hs_bac,SIRSUP)) %>%
  base::merge(pop,by.x=c("SIRSUP"),by.y=c("SIRUTA"),suffixes=c("","_pop"),all.x=T) %>%
  rename(Cod_SIRUTA2_pop=SIRSUP) 

data_town<-data %>% 
  filter(school_change==F & unitate_de_invatamant!='' & !is.na(unitate_de_invatamant)) %>% 
  group_by(an,judet_bac,town_hs_bac) %>%
  summarise(n_hs_town=length(unique(unitate_de_invatamant)),
            n_students_town_yr=mean(n_students_town_yr,na.rm=T),
            Wages_hs_bac=mean(Wages_hs_bac,na.rm=T),
            Unemployment_hs_bac=mean(Unemployment_hs_bac,na.rm=T),
            drop_hs_hs_bac=mean(drop_hs_hs_bac,na.rm=T),
            pop_1992=mean(`1992`,na.rm=T),
            pop_1993=mean(`1993`,na.rm=T),
            pop_1994=mean(`1994`,na.rm=T),
            pop_1995=mean(`1995`,na.rm=T),
            pop_1996=mean(`1996`,na.rm=T),
            pop_1997=mean(`1997`,na.rm=T),
            pop_1998=mean(`1998`,na.rm=T),
            pop_1999=mean(`1999`,na.rm=T),
            pop_2000=mean(`2000`,na.rm=T),
            pop_2001=mean(`2001`,na.rm=T),
            pop_2002=mean(`2002`,na.rm=T),
            pop_2003=mean(`2003`,na.rm=T),
            pop_2004=mean(`2004`,na.rm=T),
            pop_2005=mean(`2005`,na.rm=T),
            pop_2006=mean(`2006`,na.rm=T),
            pop_2007=mean(`2007`,na.rm=T),
            pop_2008=mean(`2008`,na.rm=T),
            pop_2009=mean(`2009`,na.rm=T),
            pop_2010=mean(`2010`,na.rm=T),
            pop_2011=mean(`2011`,na.rm=T),
            pop_2012=mean(`2012`,na.rm=T),
            pop_2013=mean(`2013`,na.rm=T),
            pop_2014=mean(`2014`,na.rm=T),
            pop_2015=mean(`2015`,na.rm=T),
            pop_2016=mean(`2016`,na.rm=T),
            pop_2017=mean(`2017`,na.rm=T),
            pop_2018=mean(`2018`,na.rm=T),
            pop_2019=mean(`2019`,na.rm=T)) %>%
  arrange(judet_bac,town_hs_bac,an) %>%
  rowwise() %>%
  #mutate(pop_2008_2019=mean(`2008`,`2009`,`2010`,`2011`,`2012`,`2013`,`2014`,`2015`,`2016`,`2017`,`2018`,`2019`,na.rm=T)) %>%
  mutate(pop_2008_2019=mean(c(pop_2008,pop_2009,pop_2010,pop_2011,pop_2012,pop_2013,pop_2014,pop_2015,pop_2016,pop_2017,pop_2018,pop_2019),na.rm=T))

data_town2<-data %>% 
  filter(school_change==F & unitate_de_invatamant!='' & !is.na(unitate_de_invatamant)) %>% 
  group_by(judet_bac,town_hs_bac) %>%
  summarise(n_hs_town=length(unique(unitate_de_invatamant)),
            n_students_town_yr=mean(n_students_town_yr,na.rm=T),
            Wages_hs_bac=mean(Wages_hs_bac,na.rm=T),
            Unemployment_hs_bac=mean(Unemployment_hs_bac,na.rm=T),
            drop_hs_hs_bac=mean(drop_hs_hs_bac,na.rm=T),
            pop_1992=mean(`1992`,na.rm=T),
            pop_1993=mean(`1993`,na.rm=T),
            pop_1994=mean(`1994`,na.rm=T),
            pop_1995=mean(`1995`,na.rm=T),
            pop_1996=mean(`1996`,na.rm=T),
            pop_1997=mean(`1997`,na.rm=T),
            pop_1998=mean(`1998`,na.rm=T),
            pop_1999=mean(`1999`,na.rm=T),
            pop_2000=mean(`2000`,na.rm=T),
            pop_2001=mean(`2001`,na.rm=T),
            pop_2002=mean(`2002`,na.rm=T),
            pop_2003=mean(`2003`,na.rm=T),
            pop_2004=mean(`2004`,na.rm=T),
            pop_2005=mean(`2005`,na.rm=T),
            pop_2006=mean(`2006`,na.rm=T),
            pop_2007=mean(`2007`,na.rm=T),
            pop_2008=mean(`2008`,na.rm=T),
            pop_2009=mean(`2009`,na.rm=T),
            pop_2010=mean(`2010`,na.rm=T),
            pop_2011=mean(`2011`,na.rm=T),
            pop_2012=mean(`2012`,na.rm=T),
            pop_2013=mean(`2013`,na.rm=T),
            pop_2014=mean(`2014`,na.rm=T),
            pop_2015=mean(`2015`,na.rm=T),
            pop_2016=mean(`2016`,na.rm=T),
            pop_2017=mean(`2017`,na.rm=T),
            pop_2018=mean(`2018`,na.rm=T),
            pop_2019=mean(`2019`,na.rm=T)) %>%
  arrange(judet_bac,town_hs_bac) %>%
  rowwise() %>%
  #mutate(pop_2008_2019=mean(`2008`,`2009`,`2010`,`2011`,`2012`,`2013`,`2014`,`2015`,`2016`,`2017`,`2018`,`2019`,na.rm=T)) %>%
  mutate(pop_2008_2019=mean(c(pop_2008,pop_2009,pop_2010,pop_2011,pop_2012,pop_2013,pop_2014,pop_2015,pop_2016,pop_2017,pop_2018,pop_2019),na.rm=T))


###
current_path<-rstudioapi::getActiveDocumentContext()$path
setwd(dirname(current_path))
rmarkdown::render("determinants.Rmd",knit_root_dir = getwd())